﻿//using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Text;

namespace YH.EA.Infrastructure.Cores
{
    /// <summary>
    /// 分页实体对象(EasyUi和jqGrid)
    /// </summary>
    public class Pagination
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public Pagination() { }

        /// <summary>
        /// 排序字段(一定要有默认值 如：CreateDate)
        /// Utility.RequestQueryString(httpContext, "sort", "CreateDate");//easyui:sort; jqgrid:sidx;
        /// </summary>
        public string SortField { get; set; } = string.Empty;

        /// <summary>
        /// 排序类型asc或者desc(默认值:"asc")
        /// Utility.RequestQueryString(httpContext, "order", "asc");//easyui:order; jqgrid:sord;
        /// </summary>
        public string Order { get; set; } = "desc";

        /// <summary>
        /// 当前页索引(默认值:1)
        /// Convert.ToInt32(Utility.RequestQueryString(httpContext, "page", "1"));
        /// </summary>
        public int page { get; set; } = 1;

        /// <summary>
        /// 当前页的行数(默认值:0)
        /// Convert.ToInt32(Utility.RequestQueryString(httpContext, "rows", "0"))
        /// </summary>
        public int limit { get; set; } = 0;

        //private IsPagination _IsPagination = IsPagination.False;
        /// <summary>
        /// 获取是否分页
        /// </summary>
        public IsPagination IsPagination
        {
            get
            {
                return limit > 0 ? IsPagination.True : IsPagination.False; //*判断是否分页
            }
        }

        //private string _Where = string.Empty;
        ///// <summary>
        ///// 动态的Where值(默认值:string.Empty)
        ///// 不加where关键字一定要以 and开始（如：and DelStatus=0）
        ///// </summary>
        //public string Where { get { return _Where; } set { _Where = value; } }

        /// <summary>
        /// 组合分页SQL
        /// </summary>
        /// <param name="originationSql">原始Sql</param>
        /// <returns></returns>
        //public string PagerStr(string originationSql, Dictionary<string, object> p2)
        public string PagerStr(string originationSql)
        {
            #region 参考
            /*
--@CurrentPageIndex:起始行数  @PageSize:本页的行数
declare @CurrentPageIndex int=1
declare @PageSize int=10
--优化过的分页语句
;with pagnation as 
(
	--start
		select 
		--top 100 percent  --在里面加Order by 语句就要此语句
		--Ult_WF_ProcessInfo_ID,Ult_WF_ProcessInfo_CreateDate,Ult_WF_ProcessInfo_Name
		ROW_NUMBER() over(order by a.CreateDate desc) as ROW_NUM,
		a.*
        from T_Project(nolock) a
        where 1=1
	--end
),T2 as
(select top 1 with ties ROW_NUM from pagnation order by ROW_NUM desc)  --记录数
select T2.ROW_NUM TOTAL_NUM, T1.* from pagnation as T1,T2 
where T1.ROW_NUM
between (@CurrentPageIndex-1)*@PageSize+1 and @CurrentPageIndex*@PageSize 
order by T1.CreateDate desc
             */
            #endregion

            string sqlString = string.Empty;
            if (IsPagination == IsPagination.True)
            {
                //p2.Add("CurrentPageIndex", Index);//注意：分页参数
                //p2.Add("PageSize", Size);//注意：分页参数

                sqlString = string.Format(@"
declare @CurrentPageIndex int={2}
declare @PageSize int={3}
;with pagnation as 
(
SELECT Row_Number() OVER(ORDER BY tbl_Data.{1}) as ROW_NUM, tbl_Data.* FROM (
--SQL语句--start--
{0}
--SQL语句--end--
) AS tbl_Data
),T2 as
(select top 1 with ties ROW_NUM from pagnation order by ROW_NUM desc)  --记录数
select T2.ROW_NUM TOTAL_NUM, T1.* from pagnation as T1,T2 
where T1.ROW_NUM
between (@CurrentPageIndex-1)*@PageSize+1 and @CurrentPageIndex*@PageSize 
order by T1.{1}
", originationSql //+ Where
, SortField + " " + Order
, page
, limit);
            }
            else
            {
                //如果是jqGrid，不管分页还是分页 都不会执行下面语句
                sqlString = string.Format(@"
SELECT Row_Number() OVER(ORDER BY tbl_Data.{1}) as ROW_NUM, tbl_Data.* FROM (
--SQL语句--start--
{0}
--SQL语句--end--
) AS tbl_Data
ORDER BY tbl_Data.{1}
", originationSql //+ Where
, SortField + " " + Order);
            }
            return sqlString;
        }


        /// <summary>
        /// 组合分页SQL
        /// </summary>
        /// <param name="originationSql">原始Sql</param>
        /// <returns></returns>
        public string PagerStrForMySql(string originationSql)
        {
            string sqlString = string.Empty;
            if (IsPagination == IsPagination.True)
            {
                //p2.Add("CurrentPageIndex", Index);//注意：分页参数
                //p2.Add("PageSize", Size);//注意：分页参数

                sqlString = $@"
select tbl.*
, (SELECT COUNT(1) FROM ({originationSql}) AS t1 ) as TOTAL_NUM
from (
{originationSql} 
) tbl
order by {SortField + " " + Order} LIMIT {(page-1)*limit}, {limit}";
            }
            else
            {
                //如果是jqGrid，不管分页还是分页 都不会执行下面语句

                sqlString = $@"{originationSql} order by {SortField + " " + Order}";
            }
            return sqlString;
        }

        /// <summary>
        /// 查询条件
        /// </summary>
        public List<Wh> Condition
        {
            get; set;
        } = new List<Wh>();

    }

    /// <summary>
    /// 分页返回对象
    /// </summary>
    public class PagResponse
    {
        /// <summary>
        /// 总数
        /// </summary>
        public int TOTAL_NUM { get; set; }

        /// <summary>
        /// 行数
        /// </summary>
        public int ROW_NUM { get; set; }
    }

    /// <summary>
    /// 判断是否分页（只限用于EasyUI Grid）
    /// </summary>
    public enum IsPagination
    {
        /// <summary>
        /// 0：允许分页
        /// </summary>
        True = 0,
        /// <summary>
        /// 1：不需要分页
        /// </summary>
        False = 1
    }

    public class Wh
    {
        public string Key
        {
            get;set;
        }

        public string Val
        {
            get;set;
        }
    }

}
